﻿using System;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq.Expressions;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Nvigorate.Data;
using Nvigorate.Data.Query;
using Nvigorate.Relational.Binding;
using Nvigorate.Relational.Binding.Interface;
using Nvigorate.Relational.Repository;
using Nvigorate.Test.TestObjects;
using MemberAssignment=Nvigorate.Relational.Binding.MemberAssignment;

namespace Nvigorate.Test.TSQL
{
    /// <summary>
    /// Summary description for UnitTest1
    /// </summary>
    [TestClass]
    public class UnitTest1
    {
        public UnitTest1()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }



        public BindingMap PersonMap;
        public BindingMap AddressMap;
        public IDatabaseProvider provider = new MSSql8Interface();

        [TestInitialize()]
        public void Initializer()
        {
            if(PersonMap == null)
            {
                string personType = typeof(Person).AssemblyQualifiedName;
                string addressType = typeof(Address).AssemblyQualifiedName;

                PersonMap = new BindingMap()
                {
                    DataSource = "test",
                    TargetName = personType
                };
                
                AddressMap = new BindingMap()
                {
                    DataSource = "test",
                    TargetName = addressType
                };


                PersonMap.AddAssignment(new MemberAssignment(true, "Person", "PersonId", personType, "Id")
                                            {
                                                Persistable = false
                                            });
                PersonMap.AddAssignment(new MemberAssignment(true, "Person", "AccountId", personType, "AccountId")
                                            {
                                                Nullable = true
                                            });
                PersonMap.AddAssignment(new MemberAssignment(
                                            false,
                                            "Person",
                                            "SocialSecurity",
                                            personType,
                                            "SocialSecurity"));
                PersonMap.AddAssignment(new MemberAssignment(
                                            false,
                                            "Person",
                                            "FirstName",
                                            personType,
                                            "FirstName"));
                PersonMap.AddAssignment(new MemberAssignment(
                                            false,
                                            "Person",
                                            "LastName",
                                            personType,
                                            "LastName"));
                PersonMap.AddAssignment(new MemberAssignment(
                                            false,
                                            "Person",
                                            "DateOfBirth",
                                            personType,
                                            "DateOfBirth"));


                AddressMap.AddAssignment(new MemberAssignment(
                                            true,
                                            "Address",
                                            "StreetName",
                                            addressType,
                                            "StreetName"));
                AddressMap.AddAssignment(new MemberAssignment(
                                            false,
                                            "Address",
                                            "StreetNumber",
                                            addressType,
                                            "StreetNumber"));
                AddressMap.AddAssignment(new MemberAssignment(
                                            true,
                                            "ZipCode",
                                            "ZipCode",
                                            addressType,
                                            "ZipCode"));
                AddressMap.AddAssignment(new MemberAssignment(
                                            false,
                                            "ZipCode",
                                            "City",
                                            addressType,
                                            "City"));
                AddressMap.AddAssignment(new MemberAssignment(
                                            false,
                                            "ZipCode",
                                            "State",
                                            addressType,
                                            "State"));

                SearchCriteria joinCriteria = Criterion.IsEqualTo("Address.ZipCode", "ZipCode.ZipCode");
                AddressMap.AddJoin(new LeftJoin() { JoinCriteria = joinCriteria, Subject = "Address", Relative = "ZipCode" });

            }
        }

        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion
        
        [TestMethod]
        public void Person_TSQL_Delete_Test()
        {
            List<string> names = new List<string>() {"one", "two", "three", "four"};
            List<string> values = new List<string>() {"uno", "dos", "trace", "quatro"};

            int index = 0;
            Dictionary<string, string> test = new Dictionary<string, string>();
        }

        [TestMethod]
        public void Person_TSQL_Insert_Test()
        {
            string rootTable = PersonMap.GetRootName();

            Person macGRUBER = new Person()
                                   {
                                       AccountId = null,
                                       FirstName = "Devin",
                                       LastName = "MacGruber",
                                       DateOfBirth = DateTime.Parse("10/31/1960"),
                                       SocialSecurity = "111-AWESOME"
                                   };

            var query = provider.Insert("Person", PersonMap.GetAssignments(), (IPersistable) macGRUBER);

            Assert.IsTrue(query.ToString() != "");
        }

        [TestMethod]
        public void Person_TSQL_Update_Test()
        {
            string rootTable = PersonMap.GetRootName();

            Person macGRUBER = new Person()
            {
                AccountId = null,
                FirstName = "Devin",
                LastName = "MacGruber",
                DateOfBirth = DateTime.Parse("10/31/1960"),
                SocialSecurity = "111-AWESOME"
            };

            macGRUBER.ClearDirtyState();

            macGRUBER.FirstName = "Francis";
            macGRUBER.Id = 1000;

            var query = provider.Update("Person", PersonMap.GetAssignments(), (IPersistable)macGRUBER);

            Assert.IsTrue(query.ToString() != "");
        }

        [TestMethod]
        public void Person_TSQL_Select_Test()
        {
            string rootTable = PersonMap.GetRootName();

            ISelectQuery query = provider.Select(rootTable, PersonMap)
                .SelectColumns(PersonMap.GetAssignments())
                .Where(Criterion.IsEqualTo("FirstName", "Alex"));

            string sql = query.ToString();

            Assert.AreEqual(query.Parameters.Count, 1);
            Assert.AreEqual(query.Parameters[0].Value, "Alex");
            Assert.AreEqual(sql, "SELECT Person.PersonId AS [Person.PersonId], Person.AccountId AS [Person.AccountId], Person.SocialSecurity AS [Person.SocialSecurity], Person.FirstName AS [Person.FirstName], Person.LastName AS [Person.LastName], Person.DateOfBirth AS [Person.DateOfBirth] FROM Person  WHERE Person.FirstName = @Person.FirstName1");
        }

        [TestMethod]
        public void Person_TSQL_Select_Paging_Test()
        {
            string rootTable = PersonMap.GetRootName();

            string sql = provider.Select(rootTable, PersonMap)
                .SelectColumns(PersonMap.GetAssignments())
                .Where(Criterion.IsEqualTo("FirstName", "Alex"))
                .Page(10, 1).ToString();

            string expected =
                @"DECLARE @pagingTable TABLE (    rowNumber bigint identity(1,1), [Person.PersonId]  INT, [Person.AccountId]  VARCHAR(8000), [Person.SocialSecurity]  VARCHAR(8000), [Person.FirstName]  VARCHAR(8000), [Person.LastName]  VARCHAR(8000), [Person.DateOfBirth]  DATETIME )  INSERT INTO @pagingTable      SELECT Person.PersonId AS [Person.PersonId], Person.AccountId AS [Person.AccountId], Person.SocialSecurity AS [Person.SocialSecurity], Person.FirstName AS [Person.FirstName], Person.LastName AS [Person.LastName], Person.DateOfBirth AS [Person.DateOfBirth] FROM Person  WHERE Person.FirstName = @Person.FirstName1 SELECT * FROM @pagingTable WHERE rowNumber > 0 AND rowNumber <= 10 SET @recordCount = (SELECT COUNT(rowNumber) FROM @pagingTable)";

            Assert.AreEqual(sql, expected);
        }

        [TestMethod]
        public void Address_TSQL_Select_Test()
        {
            //string rootTable = AddressMap.GetRootName();

            //string sql = SelectQuery.SelectFrom(rootTable, AddressMap)
            //    .SelectColumns(AddressMap.GetAssignments())
            //    .Join(AddressMap.Joins.Values.First())
            //    .Where(Criterion.IsEqualTo("City", "Franklin")).ToString();

            //string expected =
            //    @"SELECT Address.StreetName AS [Address.StreetName], Address.StreetNumber AS [Address.StreetNumber], ZipCode.ZipCode AS [ZipCode.ZipCode], ZipCode.City AS [ZipCode.City], ZipCode.State AS [ZipCode.State] FROM Address LEFT JOIN ZipCode ON Address.ZipCode = ZipCode.ZipCode WHERE ZipCode.City = @ZipCode.City1";

            //Assert.AreEqual(sql, expected);
        }

    }

    public class TSqlException : Exception
    {
        public TSqlException (string message) : base (message)
        {
            
        }
    }

    

    



    

    

    
    

    

    
}
